import { FileHelper } from '../../lib/utils/fileHelper'
import { SqlHelper } from '../../lib/utils/sqlHelper'
import { DbColumn } from '../../models/dbColumn'
import { DbDiffModel, DbDiffTable } from '../../models/dbDiffModel'
import { DbForeignKey } from '../../models/dbForeignKey'
import { DbIndex } from '../../models/dbIndex'
import { DbModel } from '../../models/dbModel'
import { DbPrimaryKey } from '../../models/dbPrimaryKey'
import { DbTable } from '../../models/dbTable'
import { DbProvider } from '../core/dbProvider'
import { DbUtils } from '../core/dbUtils'

const nunjucks = require('nunjucks')

/* eslint-disable class-methods-use-this */
/* eslint-disable prefer-template */

export class MariaDbProvider extends DbProvider {
	public getCreateTableIfNotExistsSql(model: DbModel): string {
		DbUtils.genModelNames(model)
		const env = MariaDbProvider.getEngine()
		return env.render('sql-create-tables.njk', { ifNotExists: true, withComment: true, ...model })
	}

	public getCreateTableSql(model: DbModel, withComment: boolean): string {
		DbUtils.genModelNames(model)
		const env = MariaDbProvider.getEngine()
		return env.render('sql-create-tables.njk', { ifNotExists: false, withComment, ...model })
	}

	public getDropTableSql(model: DbModel): string {
		DbUtils.genModelNames(model)
		const env = MariaDbProvider.getEngine()
		return env.render('sql-drop-tables.njk', model)
	}

	public getDropThenCreateTableSql(model: DbModel, withComment: boolean): string {
		DbUtils.genModelNames(model)
		const env = MariaDbProvider.getEngine()
		return env.render('sql-drop-then-create-tables.njk', {
			...model,
			ifNotExists: false,
			withComment,
		})
	}

	public getUpgradeSql(fromModel: DbModel, model: DbModel, withComment: boolean): string {
		DbUtils.genModelNames(model)
		const diffModel = this.getDifferent(fromModel, model)
		const env = MariaDbProvider.getEngine()
		const body = env.render('sql-upgrade-tables.njk', { ...diffModel, withComment })
		const code: string = FileHelper.readFileSync(
			FileHelper.getResourceFile('mariadb', 'procAppUpgradeDB.sql'),
		)
		return code.replace('  -- $body', body)
	}

	public static getEngine(): any {
		const env = new nunjucks.Environment(
			new nunjucks.FileSystemLoader(FileHelper.getResourceFile('mariadb')),
			{
				autoescape: false,
				trimBlocks: true,
				lstripBlocks: false,
			},
		)

		env.addFilter('toSQL', SqlHelper.toSQL)
		env.addFilter('getComment', DbUtils.getComment)
		env.addFilter('log', DbUtils.log)
		return env
	}

	/**
	 * get the different between models
	 * @param fromModel the earlier version model
	 * @param toModel the current version model
	 * @returns
	 */
	private getDifferent(fromModel: DbModel, toModel: DbModel): DbDiffModel {
		const diffModel: DbDiffModel = { diffTables: [], newTables: [] }
		const diffTables = new Map<string, DbDiffTable>()

		const fromTables = new Map<string, DbTable>()
		const fromColumns = new Map<string, DbColumn>()
		const fromIndexes = new Map<string, DbIndex>()
		const fromForeignKeys = new Map<string, DbForeignKey>()
		const fromPrimaryKeys = new Map<string, DbPrimaryKey>()

		fromModel.tables.forEach((table) => {
			fromTables.set(table.name, table)

			// existing columns
			table.columns.forEach((column) => {
				fromColumns.set(`${table.name}:Columns:${column.name}`, column)
			})

			if (table.primaryKey) {
				fromPrimaryKeys.set(
					`${table.name}:PK:${table.primaryKey.columns.replace(' ', '')}`,
					table.primaryKey,
				)
			}
			// existing indexes
			table.indexes.forEach((index) => {
				fromIndexes.set(`${table.name}:IK:${index.columns.replace(' ', '')}`, index)
			})

			// existing foreign keys
			table.foreignKeys.forEach((foreignKey) => {
				fromForeignKeys.set(
					`${table.name}:FK:${foreignKey.refTable}:${foreignKey.columns.replace(' ', '')}`,
					foreignKey,
				)
			})
		})

		toModel.tables.forEach((table) => {
			// new tables
			if (!fromTables.has(table.name)) {
				diffModel.newTables.push(table)
				return
			}

			// check columns
			table.columns.forEach((column, index) => {
				const name = `${table.name}:Columns:${column.name}`
				if (!fromColumns.has(name)) {
					const diffTable: DbDiffTable = this.getOrCreateDiffTable(
						diffTables,
						table.name,
						diffModel,
					)

					// new column and after
					diffTable.newColumns.push(column)
					if (index > 0) {
						column.afterColumn = table.columns[index - 1].name
					} else {
						column.afterColumn = undefined
					}
				}
			})

			// check primary keys
			if (table.primaryKey) {
				const name = `${table.name}:PK:${table.primaryKey.columns.replace(' ', '')}`
				if (!fromPrimaryKeys.has(name)) {
					const diffTable: DbDiffTable = this.getOrCreateDiffTable(
						diffTables,
						table.name,
						diffModel,
					)

					// new primary key
					diffTable.newPrimaryKeys = table.primaryKey
				}
			}

			// check indexes
			table.indexes.forEach((dbIndex) => {
				const name = `${table.name}:IK:${dbIndex.columns.replace(' ', '')}`
				if (!fromIndexes.has(name)) {
					const diffTable: DbDiffTable = this.getOrCreateDiffTable(
						diffTables,
						table.name,
						diffModel,
					)

					// new index
					diffTable.newIndexes.push(dbIndex)
				}
			})

			// check foreign keys
			table.foreignKeys.forEach((foreignKey) => {
				const name = `${table.name}:FK:${foreignKey.refTable}:${foreignKey.columns.replace(
					' ',
					'',
				)}`
				if (!fromForeignKeys.has(name)) {
					const diffTable: DbDiffTable = this.getOrCreateDiffTable(
						diffTables,
						table.name,
						diffModel,
					)

					// new foreign key
					diffTable.newForeignKeys.push(foreignKey)
				}
			})
		})

		return diffModel
	}

	private getOrCreateDiffTable(
		diffTables: Map<string, DbDiffTable>,
		name: string,
		diffModel: DbDiffModel,
	): DbDiffTable {
		let diffTable: DbDiffTable | undefined = diffTables.get(name)
		if (!diffTable) {
			diffTable = {
				name,
				newColumns: [],
				newForeignKeys: [],
				newIndexes: [],
			}
			diffTables.set(name, diffTable)
			diffModel.diffTables.push(diffTable)
		}
		return diffTable
	}
}
